package com.xscha.tableforge.repository;

import com.xscha.tableforge.model.entity.RelationMapping;
import org.jooq.DSLContext;
import org.jooq.Record;
import org.jooq.Result;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Optional;
import java.util.stream.Collectors;

import static com.xscha.tableforge.jooq.tables.RelationMappingTable.RELATION_MAPPING;
import static org.jooq.impl.DSL.*;

@Repository
public class RelationMappingRepository {

    private final DSLContext dsl;

    @Autowired
    public RelationMappingRepository(DSLContext dsl) {
        this.dsl = dsl;
    }

    public Optional<RelationMapping> findById(String id) {
        Record record = dsl.select()
                .from(RELATION_MAPPING)
                .where(RELATION_MAPPING.ID.eq(id))
                .fetchOne();
        
        return Optional.ofNullable(record).map(this::mapToRelationMapping);
    }

    public List<RelationMapping> findByRelationFieldIdAndSourceRecordId(String relationFieldId, String sourceRecordId) {
        Result<Record> records = dsl.select()
                .from(RELATION_MAPPING)
                .where(RELATION_MAPPING.RELATION_FIELD_ID.eq(relationFieldId))
                .and(RELATION_MAPPING.SOURCE_RECORD_ID.eq(sourceRecordId))
                .fetch();
        
        return records.stream()
                .map(this::mapToRelationMapping)
                .collect(Collectors.toList());
    }

    /**
     * 根据关联字段ID和目标记录ID查询关联映射
     */
	public List<RelationMapping> findByRelationFieldIdAndTargetRecordId(String relationFieldId, String targetRecordId) {
        Result<Record> records = dsl.select()
                .from(RELATION_MAPPING)
                .where(RELATION_MAPPING.RELATION_FIELD_ID.eq(relationFieldId))
                .and(RELATION_MAPPING.TARGET_RECORD_ID.eq(targetRecordId))
                .fetch();
        
        return records.stream()
                .map(this::mapToRelationMapping)
                .collect(Collectors.toList());
    }

    public Map<String, List<String>> findTargetRecordIdsByRelationFieldIdAndSourceRecordIds(
            String relationFieldId, List<String> sourceRecordIds) {
        Result<Record> records = dsl.select()
                .from(RELATION_MAPPING)
                .where(RELATION_MAPPING.RELATION_FIELD_ID.eq(relationFieldId))
                .and(RELATION_MAPPING.SOURCE_RECORD_ID.in(sourceRecordIds))
                .fetch();
        
        Map<String, List<String>> result = new HashMap<>();
        for (String sourceRecordId : sourceRecordIds) {
            result.put(sourceRecordId, new ArrayList<>());
        }
        
        for (Record record : records) {
            String sourceRecordId = record.get(RELATION_MAPPING.SOURCE_RECORD_ID);
            String targetRecordId = record.get(RELATION_MAPPING.TARGET_RECORD_ID);
            
            List<String> targetIds = result.get(sourceRecordId);
            if (targetIds != null) {
                targetIds.add(targetRecordId);
            }
        }
        
        return result;
    }

    public RelationMapping save(RelationMapping relationMapping) {
        dsl.insertInto(RELATION_MAPPING)
                .set(RELATION_MAPPING.ID, relationMapping.getId())
                .set(RELATION_MAPPING.RELATION_FIELD_ID, relationMapping.getRelationFieldId())
                .set(RELATION_MAPPING.SOURCE_RECORD_ID, relationMapping.getSourceRecordId())
                .set(RELATION_MAPPING.TARGET_RECORD_ID, relationMapping.getTargetRecordId())
                .execute();
        
        return relationMapping;
    }

    public List<RelationMapping> saveAll(List<RelationMapping> relationMappings) {
        if (relationMappings.isEmpty()) {
            return relationMappings;
        }
        
        // 批量插入
        org.jooq.BatchBindStep batch = dsl.batch(
                dsl.insertInto(RELATION_MAPPING,
                        RELATION_MAPPING.ID,
                        RELATION_MAPPING.RELATION_FIELD_ID,
                        RELATION_MAPPING.SOURCE_RECORD_ID,
                        RELATION_MAPPING.TARGET_RECORD_ID)
                   .values((String) null, null, null, null)
        );
        
        for (RelationMapping mapping : relationMappings) {
            batch.bind(
                    mapping.getId(),
                    mapping.getRelationFieldId(),
                    mapping.getSourceRecordId(),
                    mapping.getTargetRecordId()
            );
        }
        
        batch.execute();
        return relationMappings;
    }

    public void delete(String id) {
        dsl.deleteFrom(RELATION_MAPPING)
                .where(RELATION_MAPPING.ID.eq(id))
                .execute();
    }

    public void deleteAllByRelationFieldId(String relationFieldId) {
        dsl.deleteFrom(RELATION_MAPPING)
                .where(RELATION_MAPPING.RELATION_FIELD_ID.eq(relationFieldId))
                .execute();
    }

    public void deleteAllByRelationFieldIdAndSourceRecordId(String relationFieldId, String sourceRecordId) {
        dsl.deleteFrom(RELATION_MAPPING)
                .where(RELATION_MAPPING.RELATION_FIELD_ID.eq(relationFieldId))
                .and(RELATION_MAPPING.SOURCE_RECORD_ID.eq(sourceRecordId))
                .execute();
    }

    public void deleteByRelationFieldIdAndSourceRecordIdAndTargetRecordId(
            String relationFieldId, String sourceRecordId, String targetRecordId) {
        dsl.deleteFrom(RELATION_MAPPING)
                .where(RELATION_MAPPING.RELATION_FIELD_ID.eq(relationFieldId))
                .and(RELATION_MAPPING.SOURCE_RECORD_ID.eq(sourceRecordId))
                .and(RELATION_MAPPING.TARGET_RECORD_ID.eq(targetRecordId))
                .execute();
    }

    private RelationMapping mapToRelationMapping(Record record) {
        RelationMapping relationMapping = new RelationMapping();
        relationMapping.setId(record.get(RELATION_MAPPING.ID));
        relationMapping.setRelationFieldId(record.get(RELATION_MAPPING.RELATION_FIELD_ID));
        relationMapping.setSourceRecordId(record.get(RELATION_MAPPING.SOURCE_RECORD_ID));
        relationMapping.setTargetRecordId(record.get(RELATION_MAPPING.TARGET_RECORD_ID));
        return relationMapping;
    }
}